﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Waker.Entitys;
using System.Data.Common;
using Waker.Common;



namespace Waker.DAL
{
    public class DAL_Device : DAL_Base
    {
        #region 门禁设备
        public DataTable GetDeviceByID(string dId)
        {
            var sql = @"
SELECT * FROM tbADS_Device AS A
LEFT JOIN tbADS_ComAdapter AS B
ON A.ComAdapter_ID=B.ComAdapter_ID
WHERE A.Device_ID=@Device_ID";
            return base.GetTableBySql(sql, new En_Param("Device_ID", DbType.String, dId));
        }
        public bool SaveDevice(string Device_ID, string Physics_ID, string Device_Name, string Device_Type, string Device_Desc, string comAdapter_ID, string IP, string port, string pwd, List<ADSHalDataStruct.ADS_LogicSubDeviceInformation> listSubs)
        {
            List<DbCommand> listCmd = new List<DbCommand>();

            if (string.IsNullOrEmpty(Device_ID))
            {
                comAdapter_ID = Guid.NewGuid().ToString();
                Device_ID = Guid.NewGuid().ToString();
                var sql = @"
INSERT INTO tbADS_ComAdapter
        ( ComAdapter_ID , 
          ComAdapter_Addr ,
          ComAdapter_Port,
          ComAdapter_pwd
        )
VALUES  ( @ComAdapter_ID , 
          @ComAdapter_Addr ,
          @ComAdapter_Port,
          @ComAdapter_pwd
        )";
                var cmd = Db.GetSqlStringCommand(sql);
                Db.AddInParameter(cmd, "ComAdapter_ID", DbType.String, comAdapter_ID);
                Db.AddInParameter(cmd, "ComAdapter_Addr", DbType.String, IP);
                Db.AddInParameter(cmd, "ComAdapter_Port", DbType.String, port);
                Db.AddInParameter(cmd, "ComAdapter_pwd", DbType.String, pwd);
                listCmd.Add(cmd);
                var sql1 = @"
INSERT INTO tbADS_Device
        ( ComAdapter_ID ,
          Device_ID ,
          Physics_ID ,
          Device_Name ,
          Device_Type , 
          Device_Desc  
        )
VALUES  ( @ComAdapter_ID ,
          @Device_ID ,
          @Physics_ID ,
          @Device_Name ,
          @Device_Type , 
          @Device_Desc 
        )
";
                var cmd1 = Db.GetSqlStringCommand(sql1);
                Db.AddInParameter(cmd1, "ComAdapter_ID", DbType.String, comAdapter_ID);
                Db.AddInParameter(cmd1, "Device_ID", DbType.String, Device_ID);
                Db.AddInParameter(cmd1, "Physics_ID", DbType.String, Physics_ID);
                Db.AddInParameter(cmd1, "Device_Name", DbType.String, Device_Name);
                Db.AddInParameter(cmd1, "Device_Type", DbType.String, Device_Type);
                Db.AddInParameter(cmd1, "Device_Desc", DbType.String, Device_Desc);
                listCmd.Add(cmd1);
            }
            else
            {
                var cmdDelete2 = base.Db.GetSqlStringCommand("DELETE tbADS_IO WHERE LSubDevice_ID IN (SELECT LSubDevice_ID FROM tbADS_LSubDevice WHERE Device_ID=@Device_ID)");
                base.Db.AddInParameter(cmdDelete2, "Device_ID", DbType.String, Device_ID);
                listCmd.Add(cmdDelete2);

                var cmdDelete1 = base.Db.GetSqlStringCommand("DELETE tbADS_LSubDevice WHERE Device_ID=@Device_ID");
                base.Db.AddInParameter(cmdDelete1, "Device_ID", DbType.String, Device_ID);
                listCmd.Add(cmdDelete1);



                var sql = @"
UPDATE tbADS_ComAdapter 
SET
ComAdapter_Addr=@ComAdapter_Addr ,
ComAdapter_Port=@ComAdapter_Port,
ComAdapter_pwd=@ComAdapter_pwd
WHERE comAdapter_ID=@comAdapter_ID
";
                var cmd = Db.GetSqlStringCommand(sql);
                Db.AddInParameter(cmd, "ComAdapter_ID", DbType.String, comAdapter_ID);
                Db.AddInParameter(cmd, "ComAdapter_Addr", DbType.String, IP);
                Db.AddInParameter(cmd, "ComAdapter_Port", DbType.String, port);
                Db.AddInParameter(cmd, "ComAdapter_pwd", DbType.String, pwd);
                listCmd.Add(cmd);
                var sql1 = @"
UPDATE tbADS_Device
SET   
Physics_ID=@Physics_ID ,
Device_Name=@Device_Name ,
Device_Type=@Device_Type , 
Device_Desc=@Device_Desc  
WHERE Device_ID=@Device_ID
";
                var cmd1 = Db.GetSqlStringCommand(sql1);
                Db.AddInParameter(cmd1, "Device_ID", DbType.String, Device_ID);
                Db.AddInParameter(cmd1, "Physics_ID", DbType.String, Physics_ID);
                Db.AddInParameter(cmd1, "Device_Name", DbType.String, Device_Name);
                Db.AddInParameter(cmd1, "Device_Type", DbType.String, Device_Type);
                Db.AddInParameter(cmd1, "Device_Desc", DbType.String, Device_Desc);
                listCmd.Add(cmd1);
            }
            foreach (var sub in listSubs)
            {
                var LSubDevice_ID = Guid.NewGuid().ToString();
                var cmdInsertLSubDevice = Db.GetSqlStringCommand(
                    @"
INSERT INTO tbADS_LSubDevice
        ( LSubDevice_ID ,
          --PSubDevice_ID ,
          Physics_ID ,
          LSubDevice_Name ,
          LSubDevice_Type ,
          --LSubDevice_CommParam ,
          --LSubDevice_Desc ,
          --LSubDevice_ConfigData ,
          LSubDevice_IsStop ,
          --Reserver_Str ,
          --Reserver_Int ,
          --EventTemplate_ID,
         Device_ID
        )
VALUES  ( @LSubDevice_ID ,
          --@PSubDevice_ID ,
          @Physics_ID ,
          @LSubDevice_Name ,
          @LSubDevice_Type ,
          --@LSubDevice_CommParam ,
          --@LSubDevice_Desc ,
          --@LSubDevice_ConfigData ,
          @LSubDevice_IsStop ,
          --@Reserver_Str ,
          --@Reserver_Int ,
          --@EventTemplate_ID,
         @Device_ID
        )
");
                Db.AddInParameter(cmdInsertLSubDevice, "LSubDevice_ID", DbType.String, LSubDevice_ID);
                Db.AddInParameter(cmdInsertLSubDevice, "Physics_ID", DbType.String, sub.logicSubDeviceAddrNumber.logicSubDevNumber.ToString());
                Db.AddInParameter(cmdInsertLSubDevice, "LSubDevice_Name", DbType.String, string.Format("门{0}", sub.logicSubDeviceAddrNumber.logicSubDevNumber.ToString()));
                Db.AddInParameter(cmdInsertLSubDevice, "LSubDevice_Type", DbType.String, sub.logicSubDeviceCategory.ToString());
                Db.AddInParameter(cmdInsertLSubDevice, "LSubDevice_IsStop", DbType.Int32, 0);
                Db.AddInParameter(cmdInsertLSubDevice, "Device_ID", DbType.String, Device_ID);
                listCmd.Add(cmdInsertLSubDevice);
                if (sub.logicSubDeviceCategory == (byte)ADSHalConstant.ADS_LogicSubDeviceCategory.ADS_LSDC_LOCAL_DOOR)
                {

                    var cmdInsertIO = Db.GetSqlStringCommand(
                       @"
INSERT INTO tbADS_IO
        ( IO_ID ,
          LSubDevice_ID ,
          Physics_ID ,
          IO_Name ,
          IO_Type ,
          --IO_CommParam ,
          --IO_Desc ,
          --IO_ConfigData ,
          IO_IsStop 
          --Reserver_Str ,
          --Reserver_Int
        )
VALUES  ( @IO_ID ,
          @LSubDevice_ID ,
          @Physics_ID ,
          @IO_Name ,
          @IO_Type ,
          --@IO_CommParam ,
          --@IO_Desc ,
          --@IO_ConfigData ,
          @IO_IsStop 
          --@Reserver_Str 
          --@Reserver_Int
        )
");
                    Db.AddInParameter(cmdInsertIO, "IO_ID", DbType.String, Guid.NewGuid().ToString());
                    Db.AddInParameter(cmdInsertIO, "Physics_ID", DbType.String, Convert.ToUInt32(ADSHalConstant.ADS_IOM_DOOR).ToString());
                    Db.AddInParameter(cmdInsertIO, "LSubDevice_ID", DbType.String, LSubDevice_ID);
                    Db.AddInParameter(cmdInsertIO, "IO_Name", DbType.String, "门磁");
                    Db.AddInParameter(cmdInsertIO, "IO_Type", DbType.String, "0");
                    Db.AddInParameter(cmdInsertIO, "IO_IsStop", DbType.Int32, 0);
                    listCmd.Add(cmdInsertIO);

                    var cmdInsertIO1 = Db.GetSqlStringCommand(
                      @"
INSERT INTO tbADS_IO
        ( IO_ID ,
          LSubDevice_ID ,
          Physics_ID ,
          IO_Name ,
          IO_Type ,
          --IO_CommParam ,
          --IO_Desc ,
          --IO_ConfigData ,
          IO_IsStop 
          --Reserver_Str 
          --Reserver_Int
        )
VALUES  ( @IO_ID ,
          @LSubDevice_ID ,
          @Physics_ID ,
          @IO_Name ,
          @IO_Type ,
          --@IO_CommParam ,
          --@IO_Desc ,
          --@IO_ConfigData ,
          @IO_IsStop 
          --@Reserver_Str 
          --@Reserver_Int
        )
");
                    Db.AddInParameter(cmdInsertIO1, "IO_ID", DbType.String, Guid.NewGuid().ToString());
                    Db.AddInParameter(cmdInsertIO1, "Physics_ID", DbType.String, Convert.ToUInt32(ADSHalConstant.ADS_IOM_BUTTON).ToString());
                    Db.AddInParameter(cmdInsertIO1, "LSubDevice_ID", DbType.String, LSubDevice_ID);
                    Db.AddInParameter(cmdInsertIO1, "IO_Name", DbType.String, "开门按钮 ");
                    Db.AddInParameter(cmdInsertIO1, "IO_Type", DbType.String, "0");
                    Db.AddInParameter(cmdInsertIO1, "IO_IsStop", DbType.Int32, 0);
                    listCmd.Add(cmdInsertIO1);

                    var cmdInsertIO2 = Db.GetSqlStringCommand(
                      @"
INSERT INTO tbADS_IO
        ( IO_ID ,
          LSubDevice_ID ,
          Physics_ID ,
          IO_Name ,
          IO_Type ,
          --IO_CommParam ,
          --IO_Desc ,
          --IO_ConfigData ,
          IO_IsStop 
          --Reserver_Str 
          --Reserver_Int
        )
VALUES  ( @IO_ID ,
          @LSubDevice_ID ,
          @Physics_ID ,
          @IO_Name ,
          @IO_Type ,
          --@IO_CommParam ,
          --@IO_Desc ,
          --@IO_ConfigData ,
          @IO_IsStop 
          --@Reserver_Str 
          --@Reserver_Int
        )
");
                    Db.AddInParameter(cmdInsertIO2, "IO_ID", DbType.String, Guid.NewGuid().ToString());
                    Db.AddInParameter(cmdInsertIO2, "Physics_ID", DbType.String, Convert.ToUInt32(ADSHalConstant.ADS_IoNumber.ADS_IOM_LOCK).ToString());
                    Db.AddInParameter(cmdInsertIO2, "LSubDevice_ID", DbType.String, LSubDevice_ID);
                    Db.AddInParameter(cmdInsertIO2, "IO_Name", DbType.String, "门锁");
                    Db.AddInParameter(cmdInsertIO2, "IO_Type", DbType.String, "1");
                    Db.AddInParameter(cmdInsertIO2, "IO_IsStop", DbType.Int32, 0);
                    listCmd.Add(cmdInsertIO2);

                }
            }
            return base.ExecuteNonQuerySqlOnTrans(listCmd);
        }
        public bool CheckDevice(string ip, int port, string physics_ID)
        {
            var sql = @"
SELECT COUNT(*) FROM tbADS_Device AS A
LEFT JOIN tbADS_ComAdapter AS B
ON A.ComAdapter_ID=B.ComAdapter_ID
WHERE a.Physics_ID=@Physics_ID OR (B.ComAdapter_Addr=@ComAdapter_Addr AND B.ComAdapter_Port=@ComAdapter_Port)
";
            return Convert.ToInt32(base.GetObject(
                sql,
                new En_Param("Physics_ID", DbType.String, physics_ID),
                new En_Param("ComAdapter_Addr", DbType.String, ip.Trim()),
                new En_Param("ComAdapter_Port", DbType.String, port.ToString())
                )) < 1;
        }
        public DataSet GetDeviceAll()
        {
            return base.GetDataSetByProc("P_GetAllDeviceDs");
        }
        public DataTable GetDeviceAndComadpater()
        {
            var sql = @"
SELECT * FROM tbADS_Device AS A
LEFT JOIN tbADS_ComAdapter AS B
ON A.ComAdapter_ID=B.ComAdapter_ID
";
            return base.GetTableBySql(sql);
        }
        public DataTable GetAllDeviceIDAndName()
        {
            var sql = @"SELECT Device_ID,Device_Name FROM tbADS_Device";
            return base.GetTableBySql(sql);
        }
        public bool SetSubDeviceOpenTime(string dId, UInt32 time)
        {
            return base.ExecuteNonQuerySql(
                @"
UPDATE tbADS_LSubDevice SET OpenTime=@OpenTime
WHERE LSubDevice_ID=@LSubDevice_ID
",
  new En_Param("OpenTime", DbType.Int32, time),
  new En_Param("LSubDevice_ID", DbType.String, dId)
  ) > 0;
        }
        public DataTable GetSubDeviceByDeviceID(string deviceID)
        {
            return base.GetTableBySql("SELECT * FROM tbADS_LSubDevice WHERE Device_ID=@Device_ID",
                new En_Param("Device_ID", DbType.String, deviceID)
                );
        }
        public bool SetSubDeviceOpenTimeOut(string dId, UInt32 time)
        {
            return base.ExecuteNonQuerySql(
                @"
UPDATE tbADS_LSubDevice SET OpenTimeOut=@OpenTimeOut
WHERE LSubDevice_ID=@LSubDevice_ID
",
  new En_Param("OpenTimeOut", DbType.Int32, time),
  new En_Param("LSubDevice_ID", DbType.String, dId)
  ) > 0;
        }
        public bool ClearIsUpload()
        {
            //            var sql = @"
            //UPDATE tbEmp_Card SET IsUpLoad=0
            //UPDATE tbDepartment SET IsUpLoad=0
            //";
            return base.ExecuteNonQueryProc("P_ClearDeviceDataIsUpload") > 0;
        }
        public DataTable GetLSubDeviceById(string id)
        {
            var sql = @"
SELECT * FROM tbADS_LSubDevice  AS A 
LEFT JOIN T_NVR AS B ON A.NVR_ID=B.NVR_ID
WHERE A.LSubDevice_ID=@LSubDevice_ID
";
            return base.GetTableBySql(sql, new En_Param("LSubDevice_ID", DbType.String, id));
        }
        public bool updateSubDeviceSetting(string LSubDevice_ID, int OpenTime, int OpenTimeOut, bool IsUseNVR, string NVRIp, int NVRChannel, int NVRPort, string NVRUsrName, string NVRPwd)
        {
            var sql = @"
        UPDATE	tbADS_LSubDevice
        SET 
        OpenTime=@OpenTime,
        OpenTimeOut=@OpenTimeOut,
        IsUseNVR=@IsUseNVR,
        NVRIp=@NVRIp,
        NVRPort=@NVRPort,
        NVRUsrName=@NVRUsrName,
        NVRPwd=@NVRPwd,
        NVRChannel=@NVRChannel
        WHERE  LSubDevice_ID =@LSubDevice_ID
        ";
            var listParams = new List<En_Param>();
            listParams.Add(new En_Param("LSubDevice_ID", DbType.String, LSubDevice_ID));
            listParams.Add(new En_Param("OpenTime", DbType.Int32, OpenTime));
            listParams.Add(new En_Param("OpenTimeOut", DbType.Int32, OpenTimeOut));
            listParams.Add(new En_Param("IsUseNVR", DbType.Boolean, IsUseNVR));
            listParams.Add(new En_Param("NVRIp", DbType.String, NVRIp));
            listParams.Add(new En_Param("NVRPort", DbType.Int32, NVRPort));
            listParams.Add(new En_Param("NVRUsrName", DbType.String, NVRUsrName));
            listParams.Add(new En_Param("NVRPwd", DbType.String, NVRPwd));
            listParams.Add(new En_Param("NVRChannel", DbType.Int32, NVRChannel));

            return base.ExecuteNonQuerySql(sql, listParams) > 0;
        }
        //        public bool updateSubDeviceSetting(string LSubDevice_ID, int OpenTime, int OpenTimeOut, bool IsUseNVR, int NVR_ID, int NVRChannel, string LSubDevice_Name)
        //        {
        //            var sql = @"
        //UPDATE	tbADS_LSubDevice
        //SET 
        //OpenTime=@OpenTime,
        //OpenTimeOut=@OpenTimeOut,
        //IsUseNVR=@IsUseNVR,
        //NVR_ID=@NVR_ID,
        //NVRChannel=@NVRChannel,
        //LSubDevice_Name=@LSubDevice_Name
        //WHERE  LSubDevice_ID =@LSubDevice_ID
        //";
        //            var listParams = new List<En_Param>();
        //            listParams.Add(new En_Param("LSubDevice_ID", DbType.String, LSubDevice_ID));
        //            listParams.Add(new En_Param("OpenTime", DbType.Int32, OpenTime));
        //            listParams.Add(new En_Param("OpenTimeOut", DbType.Int32, OpenTimeOut));
        //            listParams.Add(new En_Param("IsUseNVR", DbType.Boolean, IsUseNVR));
        //            listParams.Add(new En_Param("NVR_ID", DbType.Int32, NVR_ID));
        //            listParams.Add(new En_Param("NVRChannel", DbType.Int32, NVRChannel));
        //            listParams.Add(new En_Param("LSubDevice_Name", DbType.String, LSubDevice_Name));

        //            return base.ExecuteNonQuerySql(sql, listParams) > 0;
        //        }
        public bool DeleteDevice(string devicecId)
        {
            return base.ExecuteNonQueryProc("P_DeleteDevice", new En_Param("Device_ID", DbType.String, devicecId)) > 0;
        }
        #endregion
        #region 通行时段
        public DataTable GetAllTimePeriod()
        {
            var sql = @"
SELECT * FROM T_TimePeriod AS A
LEFT JOIN tbADS_Device AS B
ON A.Device_ID=B.Device_ID
";
            return base.GetTableBySql(sql);
        }
        public bool DeleteTimePeriod(byte id)
        {
            return base.ExecuteNonQuerySql("DELETE FROM T_TimePeriod WHERE TimePeriod_ID=@TimePeriod_ID"
                , new En_Param("TimePeriod_ID", DbType.Byte, id)) > 0;
        }
        public DataTable GetTimePeriodByID(byte id)
        {
            var sql = @"
            SELECT * FROM T_TimePeriod WHERE TimePeriod_ID=@TimePeriod_ID
            ";
            return base.GetTableBySql(sql, new En_Param("TimePeriod_ID", DbType.Byte, id));
        }
        public bool SaveTimePeriod(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;

            List<En_Param> listParams = new List<En_Param>();
            if (dr["TimePeriod_ID"] == DBNull.Value || Convert.ToByte(dr["TimePeriod_ID"]) < 1)
            {
                var maxId = Convert.ToByte(base.GetObject("SELECT ISNULL((SELECT MAX(TimePeriod_ID) FROM T_TimePeriod),0)+1"));
                if (maxId >= 253)
                    return false;
                listParams.Add(new En_Param("TimePeriod_ID", DbType.Int32, maxId));
                sql = @"
INSERT INTO T_TimePeriod
        ( TimePeriod_ID ,
          Device_ID ,
          TimePeriod_Name ,
          BeginDate ,
          EndDate ,
          ValidWeek ,
          StartTime0 ,
          Endtime0 ,
          StartTime1 ,
          Endtime1 ,
          StartTime2 ,
          Endtime2 ,
          StartTime3 ,
          Endtime3 ,
          StartTime4 ,
          Endtime4 ,
          Remark  
        )
VALUES  ( @TimePeriod_ID ,
          @Device_ID ,
          @TimePeriod_Name ,
          @BeginDate ,
          @EndDate ,
          @ValidWeek ,
          @StartTime0 ,
          @Endtime0 ,
          @StartTime1 ,
          @Endtime1 ,
          @StartTime2 ,
          @Endtime2 ,
          @StartTime3 ,
          @Endtime3 ,
          @StartTime4 ,
          @Endtime4 ,
          @Remark  
        )
";
            }
            else
            {
                sql = @"
UPDATE T_TimePeriod
SET 
Device_ID=@Device_ID,
TimePeriod_Name=@TimePeriod_Name,
BeginDate=@BeginDate,
EndDate=@EndDate,
ValidWeek=@ValidWeek,
StartTime0=@StartTime0,
Endtime0=@Endtime0,
StartTime1=@StartTime1,
Endtime1=@Endtime1,
StartTime2=@StartTime2,
Endtime2=@Endtime2,
StartTime3=@StartTime3,
Endtime3=@Endtime3,
StartTime4=@StartTime4,
Endtime4=@Endtime4,
Remark=@Remark 
where TimePeriod_ID=@TimePeriod_ID
";
                listParams.Add(new En_Param("TimePeriod_ID", DbType.Int32, dr["TimePeriod_ID"]));
            }
            listParams.Add(new En_Param("Device_ID", DbType.String, dr["Device_ID"]));
            listParams.Add(new En_Param("TimePeriod_Name", DbType.String, dr["TimePeriod_Name"]));
            listParams.Add(new En_Param("BeginDate", DbType.DateTime, dr["BeginDate"]));
            listParams.Add(new En_Param("EndDate", DbType.DateTime, dr["EndDate"]));
            listParams.Add(new En_Param("ValidWeek", DbType.Byte, dr["ValidWeek"]));

            listParams.Add(new En_Param("StartTime0", DbType.DateTime, dr["StartTime0"]));
            listParams.Add(new En_Param("Endtime0", DbType.DateTime, dr["Endtime0"]));


            listParams.Add(new En_Param("StartTime1", DbType.DateTime, dr["StartTime1"]));
            listParams.Add(new En_Param("Endtime1", DbType.DateTime, dr["Endtime1"]));

            listParams.Add(new En_Param("StartTime2", DbType.DateTime, dr["StartTime2"]));
            listParams.Add(new En_Param("Endtime2", DbType.DateTime, dr["Endtime2"]));


            listParams.Add(new En_Param("StartTime3", DbType.DateTime, dr["StartTime3"]));
            listParams.Add(new En_Param("Endtime3", DbType.DateTime, dr["Endtime3"]));

            listParams.Add(new En_Param("StartTime4", DbType.DateTime, dr["StartTime4"]));
            listParams.Add(new En_Param("Endtime4", DbType.DateTime, dr["Endtime4"]));

            listParams.Add(new En_Param("Remark", DbType.String, dr["Remark"]));
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }

        #endregion
        #region 安全教育
        public DataTable GetAllSafeTimePeriod()
        {
            var sql = @"
SELECT * FROM T_SafeTimePeriod AS A
LEFT JOIN tbADS_Device AS B
ON A.Device_ID=B.Device_ID
LEFT JOIN tbADS_LSubDevice AS C
ON A.LSubDevice_ID=C.LSubDevice_ID
";
            return base.GetTableBySql(sql);
        }
        public bool DeleteSafeTimePeriod(int id)
        {
            return base.ExecuteNonQuerySql("DELETE FROM T_SafeTimePeriod WHERE Safe_ID=@Safe_ID"
                , new En_Param("Safe_ID", DbType.Int32, id)) > 0;
        }
        public DataTable GetSafeTimePeriodByID(int id)
        {
            var sql = @"
            SELECT * FROM T_SafeTimePeriod WHERE Safe_ID=@Safe_ID
            ";
            return base.GetTableBySql(sql, new En_Param("Safe_ID", DbType.Int32, id));
        }
        public bool SaveSafeTimePeriod(DataRow dr)
        {
            if (dr == null)
                return false;
            var sql = string.Empty;

            List<En_Param> listParams = new List<En_Param>();
            if (dr["Safe_ID"] == DBNull.Value || Convert.ToInt32(dr["Safe_ID"]) < 1)
            {
                sql = @"
INSERT INTO T_SafeTimePeriod
        ( SafeName ,
          Device_ID ,
          LSubDevice_ID,
          SafeWeek ,
          BeginDate ,
          EndDate ,
          BeginTime ,
          EndTime ,
          Remark
        )
VALUES  ( @SafeName ,
          @Device_ID ,
            @LSubDevice_ID,
          @SafeWeek ,
          @BeginDate ,
          @EndDate ,
          @BeginTime ,
          @EndTime ,
          @Remark
        )
";
            }
            else
            {
                sql = @"
UPDATE T_SafeTimePeriod
SET
SafeName=@SafeName,
Device_ID=@Device_ID,
LSubDevice_ID=@LSubDevice_ID,
SafeWeek=@SafeWeek,
BeginDate=@BeginDate,
EndDate=@EndDate,
BeginTime=@BeginTime,
EndTime=@EndTime,
Remark=@Remark
WHERE Safe_ID=@Safe_ID
";
                listParams.Add(new En_Param("Safe_ID", DbType.Int32, dr["Safe_ID"]));
            }
            listParams.Add(new En_Param("Device_ID", DbType.String, dr["Device_ID"]));
            listParams.Add(new En_Param("LSubDevice_ID", DbType.String, dr["LSubDevice_ID"]));
            listParams.Add(new En_Param("SafeName", DbType.String, dr["SafeName"]));
            listParams.Add(new En_Param("SafeWeek", DbType.String, dr["SafeWeek"]));
            listParams.Add(new En_Param("BeginDate", DbType.DateTime, dr["BeginDate"]));
            listParams.Add(new En_Param("EndDate", DbType.DateTime, dr["EndDate"]));

            listParams.Add(new En_Param("BeginTime", DbType.DateTime, dr["BeginTime"]));
            listParams.Add(new En_Param("EndTime", DbType.DateTime, dr["EndTime"]));

            listParams.Add(new En_Param("Remark", DbType.String, dr["Remark"]));
            return base.ExecuteNonQuerySql(sql, listParams) >= 0;
        }
        #endregion

        #region 视频管理
        public DataTable GetNVRByID(int nId)
        {
            var sql = @"
SELECT * FROM T_NVR WHERE NVR_ID=@NVR_ID";
            return base.GetTableBySql(sql, new En_Param("NVR_ID", DbType.Int32, nId));
        }
        public bool SaveNVR(int id, string name, string ip, int port, string userName, string pwd, string
             remark)
        {
            List<En_Param> listParms = new List<En_Param>();
            var sql = string.Empty;
            if (id < 1)
            {
                sql = @"
INSERT INTO T_NVR
        ( NVR_Name ,
          NVR_IP ,
          NVR_Port ,
          NVR_UserName ,
          NVR_Pwd ,
          Remark
        )
VALUES  ( @NVR_Name ,
          @NVR_IP ,
          @NVR_Port ,
          @NVR_UserName ,
          @NVR_Pwd ,
          @Remark
        )
";
            }
            else
            {
                sql = @"
UPDATE T_NVR SET
NVR_Name=@NVR_Name ,
NVR_IP=@NVR_IP ,
NVR_Port=@NVR_Port ,
NVR_UserName=@NVR_UserName ,
NVR_Pwd=@NVR_Pwd ,
Remark=@Remark
WHERE NVR_ID=@NVR_ID
";
                listParms.Add(new En_Param("NVR_ID", DbType.Int32, id));
            }
            listParms.Add(new En_Param("NVR_Name", DbType.String, name));
            listParms.Add(new En_Param("NVR_IP", DbType.String, ip));
            listParms.Add(new En_Param("NVR_Port", DbType.Int32, port));
            listParms.Add(new En_Param("NVR_UserName", DbType.String, userName));
            listParms.Add(new En_Param("NVR_Pwd", DbType.String, pwd));
            listParms.Add(new En_Param("Remark", DbType.String, remark));

            return base.ExecuteNonQuerySql(sql, listParms) > 0;
        }
        public bool DeleteNVR(int nid)
        {
            return base.ExecuteNonQuerySql("DELETE FROM T_NVR WHERE NVR_ID=@NVR_ID",
                new En_Param("NVR_ID", DbType.Int32, nid)
                ) > 0;
        }
        #endregion
    }
}
